﻿USE [NPIConsohPD]
GO
/****** Object:  Trigger [dbo].[trUpdatePalletHeader]    Script Date: 02/13/2015 10:12:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE trigger [dbo].[trUpdatePalletHeader] on [dbo].[tbt_PalletHeader]
	 for update	as
		if update(MovementType)
			begin
			declare @PalletID varchar(30),@MovementTypeStart varchar(3),@MovementtypeEnd varchar(3)
			,@DatetimeSAP datetime,@PlantSAP varchar(4)
			select @PalletID=i.PalletID,@MovementTypeStart=i.MovementType,@DatetimeSAP=i.Confirm521Datetime
				  from inserted i inner join deleted d on i.PalletID=d.PalletID ----where i.PalletID=@PalletID --
		--	set @MovementTypeStart=ISNULL(@MovementTypeStart,'521')
		-- ถ้า Update 521
		--if @MovementTypeStart='521'
		--	begin
		--		--Check Movement ปลายทาง 
				select @MovementtypeEnd=case when CreateBySite =b.SiteCode then '521' else '301' end 
					  from inserted  a inner join tbm_Warehouse b on a.WHDestination=b.WHCode
					 where PalletID =@PalletID
			--end
		set @MovementTypeStart=ISNULL(@MovementTypeStart,'521')
		 --ถ้าเงื่อนไขการรับเข้า SAP ตรงตามต้องการ site ต้นทาง ตรงกับ site ปลายทาง ค่อยให้ trigger ทำงาน
		 --ให้ insert data เข้า  exportDb.dbo.ProductMovementTemp 
		if @MovementtypeEnd=@MovementTypeStart 
				begin
					--Create Pallet
				IF NOT EXISTS(SELECT * 
							FROM [exportDb].[dbo].[ProductMovementTemp] 
							WHERE PalletID = @PalletID)
							insert into [exportDb].[dbo].[ProductMovementTemp]([PalletDetailID]
							,[PalletID]	,[ItemNo],[ProductCode],[Lot],[MachineCode],[ProductQty],[PutawayPlanQty]
							,[PutawayQty],[UOM],[BaseUnitQty],[BaseUnit],[CompanyCode],[PlantCode]
							,[PlantDestination],[CostCenter],[MachineRecipient]	,[BarcodeTag2]
							,[BarcodeTag3],[GIOrderNo],[CreateBy],[CreateDatetime]
							,[MovementType],UpdateDatetime)
							select ID,PalletID,ItemNo,ProductCode,Lot,MachineCode,ProductQty,PutawayPlanQty
							,PutawayQty,UOM,BaseUnitQty,BaseUnit,CompanyCode,PlantCode,PlantDestination
							,CostCenter,MachineRecipient,BarcodeTag2,BarcodeTag3,GIOrderNo,CreateBy
							,CreateDatetime,@MovementtypeEnd,getdate() from tbt_PalletDetail
							where PalletID=@PalletID
					else							
							update [exportDb].[dbo].[ProductMovementTemp]
							set UpdateDatetime=@DatetimeSAP,MovementType=@MovementtypeEnd
							where PalletID = @PalletID 
				end
			end
